<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@ page language="java" import="java.sql.*"%>
<%
	Class.forName("org.postgresql.Driver");
	Connection conn = DriverManager.getConnection(
		"jdbc:postgresql://localhost:5432/cse132b", "cse132blogin",
		"1234567890");
	Statement stmt;
%>
<!DOCTYPE html>
<html>
<head>
<script type="text/javascript">
	function doSomething(control) {
		document.getElementById("myForm").submit();
	}
</script>
</head>
<body>
	<form id="myForm" method="post" action="report3aiv.jsp">
	<table border="1">
		<tr>
			<td valign="top" width="40%">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="/menu.html" />
			</td>
			<td>
				
					<p align="center">
						<font size="6">Grades given to students over the years</font>
					</p>
					<table>
						<tr>
							<td>Course:</td>
							<td><select name="sltCourse" id="sltCourse"
								onchange="javascript:doSomething(this);">
									<option value="0">Select a course</option>
									<%
										stmt = conn.createStatement();
										ResultSet rsCourse = stmt.executeQuery("SELECT * FROM Courses");
										int selectCourse = 0;
										if (request.getParameter("sltCourse") != null) {
											selectCourse = Integer.parseInt(request
													.getParameter("sltCourse"));
										}
										while (rsCourse.next()) {
											if (rsCourse.getInt("course_id") == selectCourse) {
									%>
									<option selected value="<%=rsCourse.getInt("course_id")%>"><%=rsCourse.getString("course_number")%></option>
									<%
										} else {
									%>
									<option value="<%=rsCourse.getInt("course_id")%>"><%=rsCourse.getString("course_number")%></option>
									<%
										}
										}
										rsCourse.close();
										stmt.close();
									%>
							</select></td>
						</tr>											
					</table>
					<br>
					<table border="1">
						<tr>
							<th>Grade Letter</th>
							<th>Count</th>
						</tr>
						<%
							stmt = conn.createStatement();
							ResultSet rsReport = stmt.executeQuery("SELECT enroll.grade_letter, COUNT(*) AS GradeLetterCount FROM courses, classes, teaches, faculty, enroll WHERE courses.course_id = classes.course_id  AND classes.class_id = teaches.section_id AND teaches.faculty_id = faculty.faculty_id AND enroll.section_id = classes.class_id AND courses.course_id=" + String.valueOf(selectCourse) + " GROUP BY grade_letter");

							while (rsReport.next()) {
						%>
						<tr>
						<td><%=rsReport.getString("grade_letter")%></td>
						<td><%=rsReport.getString("GradeLetterCount")%></td>
						</tr>
						<%
							}						
							rsReport.close();
							stmt.close();
						%>
					</table>
					<input type="hidden" id="ACTION" name="ACTION" value="None">
					<input type="hidden" id="PARAMETER" name="PARAMETER" value="">

			</td>
		</tr>
	</table>
					</form>
</body>
</html>
<%
	// Close the Connection
	conn.close();
%>